----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      name:  <unnamed>
       log:  F:/Dropbox (UBC-Umich)/Research_Projects/Project Javier and Natalia/TFP firm/TFP_firm_data_historical/dataverse_files/code/logs/table_B1_affiliates_parents_bysector.
> log
  log type:  text
 opened on:  16 Dec 2022, 22:07:14

. *-------------------------------------------------------------------------------
. 
. 
. 
. *Number of foreign affiliates by sector
. *-------------------------------------------------------------------
. use "${data}/sin_firmlevel_naics_gravityLF.dta", clear

. 
. keep if isocode=="DK" | isocode=="JP" | isocode=="IT" | isocode=="KR" | isocode=="DE" | isocode=="FR" | isocode=="ES" |  isocode=="MX" |  isocode=="GB" |  isocode=="PL" |  isoc
> ode=="GR" |  isocode=="NL" |  isocode=="RO" |  isocode=="BE" |  isocode=="AT" |  isocode=="PT" |  isocode=="FI" |  isocode=="SE" |  isocode=="CZ" |  isocode=="SK" |  isocode=="
> BG" |  isocode=="HU" |  isocode=="HR" |  isocode=="LV" |  isocode=="SI" |  isocode=="LT" |  isocode=="EE" 
(67,979 observations deleted)

. keep if year==2016
(609,077 observations deleted)

. drop if sin_sales==. | sin_sales==0
(0 observations deleted)

. 
. *keep MNCs only
. by year sector guo_bvd isocode, sort: gen a=_n==1

. replace a=0 if isocode==hq
(13,362 real changes made)

. by year sector guo_bvd, sort: egen b=total(a)

. tab b 

          b |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |        868        1.18        1.18
          1 |     17,666       23.99       25.17
          2 |     14,593       19.82       44.99
          3 |      8,474       11.51       56.50
          4 |      5,765        7.83       64.33
          5 |      4,537        6.16       70.49
          6 |      3,712        5.04       75.53
          7 |      3,000        4.07       79.61
          8 |      2,647        3.60       83.20
          9 |      2,084        2.83       86.03
         10 |      1,788        2.43       88.46
         11 |      1,285        1.75       90.21
         12 |      1,205        1.64       91.84
         13 |      1,116        1.52       93.36
         14 |        821        1.12       94.48
         15 |        988        1.34       95.82
         16 |        512        0.70       96.51
         17 |        687        0.93       97.45
         18 |        439        0.60       98.04
         19 |        567        0.77       98.81
         20 |        328        0.45       99.26
         21 |        214        0.29       99.55
         22 |         68        0.09       99.64
         23 |        190        0.26       99.90
         24 |         75        0.10      100.00
------------+-----------------------------------
      Total |     73,629      100.00

. keep if b>=2
(18,534 observations deleted)

. drop a b 

. tab isocode 

    isocode |      Freq.     Percent        Cum.
------------+-----------------------------------
         AT |      1,756        3.19        3.19
         BE |      2,961        5.37        8.56
         BG |        739        1.34        9.90
         CZ |      2,561        4.65       14.55
         DE |      4,309        7.82       22.37
         DK |        975        1.77       24.14
         EE |        709        1.29       25.43
         ES |      4,347        7.89       33.32
         FI |      1,578        2.86       36.18
         FR |      5,374        9.75       45.94
         GB |      5,531       10.04       55.98
         GR |        530        0.96       56.94
         HR |        837        1.52       58.46
         HU |      1,306        2.37       60.83
         IT |      4,932        8.95       69.78
         JP |        610        1.11       70.89
         KR |        931        1.69       72.58
         LT |        463        0.84       73.42
         LV |        689        1.25       74.67
         MX |        145        0.26       74.93
         NL |      1,239        2.25       77.18
         PL |      3,439        6.24       83.42
         PT |      1,795        3.26       86.68
         RO |      2,042        3.71       90.39
         SE |      3,059        5.55       95.94
         SI |        625        1.13       97.07
         SK |      1,613        2.93      100.00
------------+-----------------------------------
      Total |     55,095      100.00

. keep if isocode!=hq
(4,446 observations deleted)

. 
. tempfile temp

. save `temp', replace 
(note: file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001e.tmp not found)
file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001e.tmp saved

. 
. 
. use `temp', clear 

. collapse (count) fa_sales=sin_sales fa_emp=sin_emp fa_va=sin_va, by(year sector1 sector)

. drop if sector=="Public_Administration (O)"
(1 observation deleted)

. 
. replace sector1="1.Other goods" if sector1=="Other_Goods (A-B-D-E-F)"
(3 real changes made)

. replace sector1="2.Manufacturing" if sector1=="Manufacturing (C)"
(6 real changes made)

. replace sector1="3.Services" if sector1=="Market_Services (G-H-I-J-K-M-N-R-S-T)"
(6 real changes made)

. replace sector1="4.Other sectors" if sector1=="Non-Market Economy"
(3 real changes made)

. 
. replace sector="1.Agriculture and Mining" if sector=="AgrMining (A-B)"
(1 real change made)

. replace sector="2.Construction" if sector=="Construction (F)"
(1 real change made)

. replace sector="3.Electricity" if sector=="Electricity (D-E)"
(1 real change made)

. 
. replace sector="4.Basic Metals" if sector=="Basic Metals (24-25)"
(1 real change made)

. replace sector="3.Chemicals, Petroleum and Plastic" if sector=="ChePetPla (19-23)"
variable sector was str26 now str34
(1 real change made)

. replace sector="5.Electrical Equipment and Machinery" if sector=="ElecMach (26-28)"
variable sector was str34 now str36
(1 real change made)

. replace sector="1.Food and Beverages" if sector=="Food (10-12)"
(1 real change made)

. replace sector="2.Textiles, Apparel and Wood" if sector=="TexWood (13-18)"
(1 real change made)

. replace sector="6.Transport Equipment and Other Manufacturing" if sector=="TranspOtherManuf (29-33)"
variable sector was str36 now str45
(1 real change made)

. 
. replace sector="6.Accommodation and Recreation" if sector=="Accomod_Recreat (I-R-S)"
(1 real change made)

. replace sector="4.Financial and Insurance Services" if sector=="Financial_Insurance (K)"
(1 real change made)

. replace sector="3.Information" if sector=="Information (J)"
(1 real change made)

. replace sector="5.Support Services" if sector=="Support_Services (M-N)"
(1 real change made)

. replace sector="2.Transportation and Storage" if sector=="Transportation_Storage (H)"
(1 real change made)

. replace sector="1.Wholesale Trade and Retail Trade" if sector=="Wholesale_Retail (G)"
(1 real change made)

. 
. replace sector="3.Education" if sector=="Education (P)"
(1 real change made)

. replace sector="2.Health" if sector=="Health (Q)"
(1 real change made)

. replace sector="1.Real Estate" if sector=="Real_Estate (L)"
(1 real change made)

. 
. sort sector1 sector

. format %9.0fc fa*

. 
. tempfile temp_foreign_affiliates

. save `temp_foreign_affiliates', replace
(note: file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001f.tmp not found)
file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001f.tmp saved

. 
. 
. 
. *Number of parents by sector
. *-------------------------------------------------------------------
. use "${data}/sin_firmlevel_naics_gravityLF.dta", clear

. 
. keep if isocode=="DK" | isocode=="JP" | isocode=="IT" | isocode=="KR" | isocode=="DE" | isocode=="FR" | isocode=="ES" |  isocode=="MX" |  isocode=="GB" |  isocode=="PL" |  isoc
> ode=="GR" |  isocode=="NL" |  isocode=="RO" |  isocode=="BE" |  isocode=="AT" |  isocode=="PT" |  isocode=="FI" |  isocode=="SE" |  isocode=="CZ" |  isocode=="SK" |  isocode=="
> BG" |  isocode=="HU" |  isocode=="HR" |  isocode=="LV" |  isocode=="SI" |  isocode=="LT" |  isocode=="EE" 
(67,979 observations deleted)

. keep if year==2016
(609,077 observations deleted)

. drop if sin_sales==. | sin_sales==0
(0 observations deleted)

. 
. *keep MNCs only
. by year sector guo_bvd isocode, sort: gen a=_n==1

. replace a=0 if isocode==hq
(13,362 real changes made)

. by year sector guo_bvd, sort: egen b=total(a)

. tab b 

          b |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |        868        1.18        1.18
          1 |     17,666       23.99       25.17
          2 |     14,593       19.82       44.99
          3 |      8,474       11.51       56.50
          4 |      5,765        7.83       64.33
          5 |      4,537        6.16       70.49
          6 |      3,712        5.04       75.53
          7 |      3,000        4.07       79.61
          8 |      2,647        3.60       83.20
          9 |      2,084        2.83       86.03
         10 |      1,788        2.43       88.46
         11 |      1,285        1.75       90.21
         12 |      1,205        1.64       91.84
         13 |      1,116        1.52       93.36
         14 |        821        1.12       94.48
         15 |        988        1.34       95.82
         16 |        512        0.70       96.51
         17 |        687        0.93       97.45
         18 |        439        0.60       98.04
         19 |        567        0.77       98.81
         20 |        328        0.45       99.26
         21 |        214        0.29       99.55
         22 |         68        0.09       99.64
         23 |        190        0.26       99.90
         24 |         75        0.10      100.00
------------+-----------------------------------
      Total |     73,629      100.00

. keep if b>=2
(18,534 observations deleted)

. drop a b 

. tab isocode 

    isocode |      Freq.     Percent        Cum.
------------+-----------------------------------
         AT |      1,756        3.19        3.19
         BE |      2,961        5.37        8.56
         BG |        739        1.34        9.90
         CZ |      2,561        4.65       14.55
         DE |      4,309        7.82       22.37
         DK |        975        1.77       24.14
         EE |        709        1.29       25.43
         ES |      4,347        7.89       33.32
         FI |      1,578        2.86       36.18
         FR |      5,374        9.75       45.94
         GB |      5,531       10.04       55.98
         GR |        530        0.96       56.94
         HR |        837        1.52       58.46
         HU |      1,306        2.37       60.83
         IT |      4,932        8.95       69.78
         JP |        610        1.11       70.89
         KR |        931        1.69       72.58
         LT |        463        0.84       73.42
         LV |        689        1.25       74.67
         MX |        145        0.26       74.93
         NL |      1,239        2.25       77.18
         PL |      3,439        6.24       83.42
         PT |      1,795        3.26       86.68
         RO |      2,042        3.71       90.39
         SE |      3,059        5.55       95.94
         SI |        625        1.13       97.07
         SK |      1,613        2.93      100.00
------------+-----------------------------------
      Total |     55,095      100.00

. keep if isocode==hq
(50,649 observations deleted)

. 
. collapse (count) pa_sales=sin_sales pa_emp=sin_emp pa_va=sin_va, by(year sector1 sector)

. drop if sector=="Public_Administration (O)"
(1 observation deleted)

. 
. replace sector1="1.Other goods" if sector1=="Other_Goods (A-B-D-E-F)"
(3 real changes made)

. replace sector1="2.Manufacturing" if sector1=="Manufacturing (C)"
(6 real changes made)

. replace sector1="3.Services" if sector1=="Market_Services (G-H-I-J-K-M-N-R-S-T)"
(6 real changes made)

. replace sector1="4.Other sectors" if sector1=="Non-Market Economy"
(3 real changes made)

. 
. replace sector="1.Agriculture and Mining" if sector=="AgrMining (A-B)"
(1 real change made)

. replace sector="2.Construction" if sector=="Construction (F)"
(1 real change made)

. replace sector="3.Electricity" if sector=="Electricity (D-E)"
(1 real change made)

. 
. replace sector="4.Basic Metals" if sector=="Basic Metals (24-25)"
(1 real change made)

. replace sector="3.Chemicals, Petroleum and Plastic" if sector=="ChePetPla (19-23)"
variable sector was str26 now str34
(1 real change made)

. replace sector="5.Electrical Equipment and Machinery" if sector=="ElecMach (26-28)"
variable sector was str34 now str36
(1 real change made)

. replace sector="1.Food and Beverages" if sector=="Food (10-12)"
(1 real change made)

. replace sector="2.Textiles, Apparel and Wood" if sector=="TexWood (13-18)"
(1 real change made)

. replace sector="6.Transport Equipment and Other Manufacturing" if sector=="TranspOtherManuf (29-33)"
variable sector was str36 now str45
(1 real change made)

. 
. replace sector="6.Accommodation and Recreation" if sector=="Accomod_Recreat (I-R-S)"
(1 real change made)

. replace sector="4.Financial and Insurance Services" if sector=="Financial_Insurance (K)"
(1 real change made)

. replace sector="3.Information" if sector=="Information (J)"
(1 real change made)

. replace sector="5.Support Services" if sector=="Support_Services (M-N)"
(1 real change made)

. replace sector="2.Transportation and Storage" if sector=="Transportation_Storage (H)"
(1 real change made)

. replace sector="1.Wholesale Trade and Retail Trade" if sector=="Wholesale_Retail (G)"
(1 real change made)

. 
. replace sector="3.Education" if sector=="Education (P)"
(1 real change made)

. replace sector="2.Health" if sector=="Health (Q)"
(1 real change made)

. replace sector="1.Real Estate" if sector=="Real_Estate (L)"
(1 real change made)

. 
. sort sector1 sector

. format %9.0fc pa*

. 
. tempfile temp_parents

. save `temp_parents', replace
(note: file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001g.tmp not found)
file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001g.tmp saved

. 
. 
. 
. *Merge foreing affiliates and parents by NAICS2 
. *-------------------------------------------------
. use `temp_foreign_affiliates', clear

. merge 1:1 year sector using `temp_parents'

    Result                           # of obs.
    -----------------------------------------
    not matched                             0
    matched                                18  (_merge==3)
    -----------------------------------------

. drop _merge

. 
. sort sector1 sector

. format %9.0fc fa* pa*

. 
. export delimited using "${rappendix}/table_B1_affiliates_parents_bysector.csv", datafmt replace
(note: file F:/Dropbox (UBC-Umich)/Research_Projects/Project Javier and Natalia/TFP firm/TFP_firm_data_historical/dataverse_files/results/appendix/table_B1_affiliates_parents_bys
> ector.csv not found)
file F:/Dropbox (UBC-Umich)/Research_Projects/Project Javier and Natalia/TFP firm/TFP_firm_data_historical/dataverse_files/results/appendix/table_B1_affiliates_parents_bysector.c
> sv saved

. 
. log close 
      name:  <unnamed>
       log:  F:/Dropbox (UBC-Umich)/Research_Projects/Project Javier and Natalia/TFP firm/TFP_firm_data_historical/dataverse_files/code/logs/table_B1_affiliates_parents_bysector.
> log
  log type:  text
 closed on:  16 Dec 2022, 22:07:18
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
